﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.Common;
//using Microsoft.Practices.EnterpriseLibrary.Data;

namespace NET.Library.Data
{
    interface IDB
    {
        //#region Fields
        ///// <summary>
        ///// Private variable used by database connection string property
        ///// </summary>
        //private string _connectionString;

        //#endregion

        #region Properties
        /// <summary>
        /// Property to store and retrieve database connection string
        /// </summary>
        /// 
         string ConnectionString
        { get; set; }
        #endregion

        //////#region Constructors
        ///////// <summary>
        ///////// DB Constructor
        ///////// </summary>
        ///////// <param name="connectionString"></param>
        //////void DB(string connectionString);

        ///////// <summary>
        ///////// DB Constructor
        ///////// </summary>
        //////void DB();
        //////#endregion

        #region Instance Methods
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the results as a DataSet.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteDataSet(DbCommand dbCommand);
        //{
        //    //Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        //    //try
        //    //{
        //    //    return db.ExecuteDataSet(dbCommand);
        //    //}
        //    //finally
        //    //{
        //    //    dbCommand.Connection.Close();
        //    //    dbCommand.Dispose();
        //    //    if (db != null) db = null;
        //    //}
        //}


        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        ///////// </summary>
        ///////// <param name="sqlCommand">The SqlCommand object to use.</param>
        ///////// <returns>Result as a DataSet.</returns>
        /////////----------------------------------------------------------------------------------
        //////public DataSet DBExecuteDataSet(SqlCommand sqlCommand)
        //////{
        //////    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        //////    try
        //////    {
        //////        return db.ExecuteDataSet(sqlCommand);
        //////    }
        //////    finally
        //////    {
        //////        sqlCommand.Connection.Close();
        //////        sqlCommand.Dispose();
        //////        if (db != null) db = null;
        //////    }
        //////}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a SqlCommand object and transAction and returns the results as a DataSet.
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns>Result as a DataSet.</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteDataSetWithTransAction(string storedProcedureName, params object[] parameterValues);
        //{
        //    DataSet ds = null;
        //    Database db;
        //    DbCommand dbCommand;
        //    db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    DbConnection con = db.CreateConnection();
        //    con.Open();
        //    DbTransaction trans = con.BeginTransaction();

        //    try
        //    {

        //        ds = db.ExecuteDataSet(dbCommand, trans);
        //        trans.Commit();
        //    }
        //    catch (Exception ex)
        //    {
        //        trans.Rollback();
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }

        //    return ds;
        //}

       ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        /// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteStoredProc(string storedProcName, DataSet dsProcParams);
        //{
        //    SqlCommand sqlCommd = new SqlCommand();

        //    try
        //    {
        //        sqlCommd.CommandType = CommandType.StoredProcedure;
        //        sqlCommd.CommandText = storedProcName;

        //        if (dsProcParams.Tables.Count > 0)
        //        {
        //            PopulateSQLCommandFromDataTable(sqlCommd, dsProcParams.Tables[0]);
        //        }

        //        return DBExecuteDataSet(sqlCommd);
        //    }
        //    finally
        //    {
        //        sqlCommd.Dispose();
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteStoredProc(string storedProcedureName, string[] tableNames);
        //{
        //    object[] emptyArray = new object[0];
        //    return DBExecuteStoredProc(storedProcedureName, emptyArray, tableNames);
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        /// </summary>
        /// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        /// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        /// <param name="parameterValues"></param>
        /// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        /// <returns>Dataset from the website database</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteStoredProc(string storedProcedureName, object[] parameterValues, string[] tableNames);
        //{
        //    DataSet returnDS = DBExecuteDataSet(storedProcedureName, parameterValues);
        //    for (int i = 0; i < returnDS.Tables.Count; i++)
        //    {
        //        if (tableNames[i] != null)
        //        {
        //            returnDS.Tables[i].TableName = tableNames[i].Trim();
        //        }
        //    }
        //    return returnDS;
        //}
        
        // IDataReader
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
         IDataReader DBExecuteReader(string storedProcedureName);
        //{
        //    return DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName);
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
         IDataReader DBExecuteReader(string storedProcedureName, params object[] parameterValues);
        //{
        //    return (SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        //}

        ////////SqlDataReader
        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public SqlDataReader DBExecuteSqlReader(string storedProcedureName)
        //////{
        //////    return (SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName);
        //////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////public SqlDataReader DBExecuteSqlReader(string storedProcedureName, params object[] parameterValues)
        //////{
        //////    return (SqlDataReader)DatabaseFactory.CreateDatabase(this.ConnectionString).ExecuteReader(storedProcedureName, parameterValues);
        //////}

        //MySqlDataReader
        /////----------------------------------------------------------------------------------
        ///// <summary>
        ///// Executes a stored procedure with parameterValues and returns the results as a MySqlDataReader.
        ///// </summary>
        ///// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///// <returns></returns>
        /////----------------------------------------------------------------------------------
        //public MySqlDataReader DBExecuteMySqlReader(string storedProcedureName)
        //{
        //    //return (MySqlDataReader)DatabaseFactory.CreateDatabase().ExecuteReader(storedProcedureName);
        //    return MySqlHelper.ExecuteReader(this.ConnectionString, storedProcedureName);
        //}


        //DataSet
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns>DataSet</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteDataSet(string storedProcedureName);
        //{
        //    Microsoft.Practices.EnterpriseLibrary.Data.Database db;
        //    DbCommand dbCommand;

        //    db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    dbCommand = db.GetStoredProcCommand(storedProcedureName);
        //    try
        //    {
        //        return db.ExecuteDataSet(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>DataSet</returns>
        ///----------------------------------------------------------------------------------
         DataSet DBExecuteDataSet(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db;
        //    DbCommand dbCommand;

        //    db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    try
        //    {
        //        return db.ExecuteDataSet(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        //ExecuteNonQuery
        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns></returns>
        ///----------------------------------------------------------------------------------
         int DBExecuteNonQuery(string storedProcedureName);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        //    try
        //    {
        //        return db.ExecuteNonQuery(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
         int DBExecuteNonQuery(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    try
        //    {
        //        return db.ExecuteNonQuery(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure with a DbCommand object and returns the number of rows affected.
        /// </summary>
        /// <param name="dbCommand">The DbCommand object to use.</param>
        /// <returns>The number of rows affected.</returns>
        ///----------------------------------------------------------------------------------
         int DBExecuteNonQuery(DbCommand dbCommand);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    try
        //    {
        //        return db.ExecuteNonQuery(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        ///////// </summary>
        ///////// <param name="sqlCommand">The SqlCommand object to use.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        ////// int DBExecuteNonQuery(SqlCommand sqlCommand);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        ////////    try
        ////////    {
        ////////        return db.ExecuteNonQuery(sqlCommand);
        ////////    }
        ////////    finally
        ////////    {
        ////////        sqlCommand.Connection.Close();
        ////////        sqlCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        //ExecuteScalar
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <returns>object</returns>
         object DBExecuteScalar(string storedProcedureName);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        //    try
        //    {
        //        return db.ExecuteScalar(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns></returns>
         object DBExecuteScalar(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    try
        //    {
        //        return db.ExecuteScalar(dbCommand);
        //    }
        //    finally
        //    {
        //        dbCommand.Connection.Close();
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>True if the query was executed sucessfully, else false.</returns>
        ///----------------------------------------------------------------------------------
         bool DBExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);
        //    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        //    try
        //    {
        //        return db.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
        //    }
        //    finally
        //    {
        //        dbCommand.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Executes a stored procedure and returns the value of the parameter called ReturnValue.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /// The parameter values must be in call order as they appear in the stored procedure.</param>
        /// <returns>The value of the parameter called ReturnValue.</returns>
        ///----------------------------------------------------------------------------------
         int DBExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        //    DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);

        //    try
        //    {
        //        return (int)db.ExecuteScalar(dbCmd);
        //    }
        //    finally
        //    {
        //        dbCmd.Connection.Close();
        //        dbCmd.Dispose();
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
         DbCommand DBGetStoredProcCommand(string storedProcedureName);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        //    try
        //    {
        //        return db.GetStoredProcCommand(storedProcedureName);
        //    }
        //    finally
        //    {
        //        if (db != null) db = null;
        //    }
        //}

        ///----------------------------------------------------------------------------------
        /// <summary>
        /// Wrapper method to return a StoredProcCommand.
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /// <param name="parameterValues"></param>
        /// <returns>DbCommand</returns>
        ///----------------------------------------------------------------------------------
         DbCommand DBGetStoredProcCommand(string storedProcedureName, params object[] parameterValues);
        //{
        //    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        //    try
        //    {
        //        return db.GetStoredProcCommand(storedProcedureName, parameterValues);
        //    }
        //    finally
        //    {
        //        if (db != null) db = null;
        //    }
        //}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Wrapper method to return a StoredProcCommand.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns>SqlCommand</returns>
        /////////----------------------------------------------------------------------------------
        ////// SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        ////////    try
        ////////    {
        ////////        return (SqlCommand)db.GetStoredProcCommand(storedProcedureName);
        ////////    }
        ////////    finally
        ////////    {
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Wrapper method to return a StoredProcCommand.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues"></param>
        ///////// <returns>SqlCommand</returns>
        /////////----------------------------------------------------------------------------------
        ////// SqlCommand DBGetStoredProcSqlCommand(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase(this.ConnectionString);

        ////////    try
        ////////    {
        ////////        return (SqlCommand)db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    }
        ////////    finally
        ////////    {
        ////////        if (db != null) db = null;
        ////////    }
        ////////}
        #endregion

        //Interfaces do not allow methods to be used as static in their inheriting classes:
        //Basically the interface is the contract between the consumer and the provider, and a static method belongs to the class, and not each instance of the class as such.
        //////#region Static Methods

        //////#region ExecuteReader

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure and returns the results as an IDataReader.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <returns>Result as an IDataReader</returns>
        ///////// <example>
        ///////// IDataReader dr = DB.ExecuteReader(SP_ADDRESS_GETADDRESSBYID, addressId);
        /////////    try
        /////////    {
        /////////        while (dr.Read())
        /////////        {
        /////////            Address newAddress = new Address(
        /////////                GetDBInt(dr, "ID"),
        /////////                GetDBString(dr, "Address1"),
        /////////                GetDBString(dr, "Address2"),
        /////////                GetDBString(dr, "Address3"),
        /////////                GetDBString(dr, "Town"),
        /////////                GetDBInt(dr, "CountyID"),
        /////////                GetDBInt(dr, "CountryID"),
        /////////                GetDBString(dr, "Postcode"));
        /////////            return newAddress;
        /////////        }
        /////////
        /////////        return null;
        /////////    }
        /////////    finally
        /////////    {
        /////////        dr.Close();
        /////////    }
        ///////// </example>
        /////////----------------------------------------------------------------------------------
        //////IDataReader ExecuteReader();

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with parameterValues and returns the results as an IDataReader.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the Stored Procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>Result as an IDataReader</returns>
        /////////----------------------------------------------------------------------------------
        //////IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues);
        
        //////#endregion

        //////#region ExecuteDataSet

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure and returns the results as a DataSet.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns>Result as a DataSet</returns>
        /////////----------------------------------------------------------------------------------
        //////  DataSet ExecuteDataSet(string storedProcedureName);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();

        ////////    DbCommand dbCommand;

        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName);
        ////////    try
        ////////    {
        ////////        DataSet ds = db.ExecuteDataSet(dbCommand);
        ////////        return ds;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with parameterValues and returns the results as a DataSet.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>Result as a DataSet.</returns>
        /////////----------------------------------------------------------------------------------
        //////  DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();

        ////////    DbCommand dbCommand;
        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    try
        ////////    {
        ////////        DataSet ds = db.ExecuteDataSet(dbCommand);
        ////////        return ds;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with a DbCommand object and returns the results as a DataSet.
        ///////// </summary>
        ///////// <param name="dbCommand">The DbCommand object to use.</param>
        ///////// <returns>Result as a DataSet.</returns>
        /////////----------------------------------------------------------------------------------
        //////  DataSet ExecuteDataSet(DbCommand dbCommand);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();

        ////////    try
        ////////    {
        ////////        DataSet ds = db.ExecuteDataSet(dbCommand);
        ////////        return ds;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// Executes a stored procedure with a SqlCommand object and returns the results as a DataSet.
        /////////////// </summary>
        /////////////// <param name="sqlCommand">The SqlCommand object to use.</param>
        /////////////// <returns>Result as a DataSet.</returns>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static DataSet ExecuteDataSet(SqlCommand sqlCommand);
        //////////////{
        //////////////    Database db = DatabaseFactory.CreateDatabase();

        //////////////    try
        //////////////    {
        //////////////        DataSet ds = db.ExecuteDataSet(sqlCommand);
        //////////////        return ds;
        //////////////    }
        //////////////    finally
        //////////////    {
        //////////////        sqlCommand.Connection.Close();
        //////////////        sqlCommand.Dispose();
        //////////////        if (db != null) db = null;
        //////////////    }
        //////////////}

        //////#endregion

        //////#region ExecuteScalar

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// ExecuteScalar
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns>object</returns>
        /////////----------------------------------------------------------------------------------
        //////  object ExecuteScalar(string storedProcedureName);
        ////////{
        ////////    Database db;
        ////////    DbCommand dbCommand;
        ////////    db = DatabaseFactory.CreateDatabase();
        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName);
        ////////    try
        ////////    {
        ////////        object returnObject = db.ExecuteScalar(dbCommand);
        ////////        //dbCommand.Connection.Close();
        ////////        return returnObject;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// ExecuteScalar
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>object</returns>
        /////////----------------------------------------------------------------------------------
        //////  object ExecuteScalar(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db;
        ////////    DbCommand dbCommand;
        ////////    db = DatabaseFactory.CreateDatabase();
        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    try
        ////////    {
        ////////        object returnObject = db.ExecuteScalar(dbCommand);
        ////////        //dbCommand.Connection.Close();
        ////////        return returnObject;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////    //return DatabaseFactory.CreateDatabase().ExecuteScalar(storedProcedureName, parameterValues);
        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// Executes a stored procedure with a SqlCommand object and returns a single result as an object.
        /////////////// </summary>
        /////////////// <param name="sqlCommand">The SqlCommand object to use.</param>
        /////////////// <returns>object</returns>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static object ExecuteScalar(SqlCommand sqlCommand)
        ////////////{
        ////////////    Database db;
        ////////////    db = DatabaseFactory.CreateDatabase();
        ////////////    try
        ////////////    {
        ////////////        return db.ExecuteScalar(sqlCommand);
        ////////////    }
        ////////////    finally
        ////////////    {
        ////////////        sqlCommand.Connection.Close();
        ////////////        sqlCommand.Dispose();
        ////////////        if (db != null) db = null;
        ////////////    }
        ////////////}

        ////////////public static bool ExecuteScalarBool(string spName, DataSet dsParams)
        ////////////{
        ////////////    SqlCommand sqlComm = new SqlCommand();
        ////////////    try
        ////////////    {
        ////////////        sqlComm.CommandType = CommandType.StoredProcedure;
        ////////////        PopulateSQLCommandFromDataTable(sqlComm, dsParams.Tables[0]);
        ////////////        sqlComm.CommandText = spName;
        ////////////        return (bool)DB.ExecuteScalar(sqlComm);
        ////////////    }
        ////////////    finally
        ////////////    {
        ////////////        sqlComm.Dispose();
        ////////////    }
        ////////////}

        //////#endregion

        //////#region Misc

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns></returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQuery(string storedProcedureName);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        ////////    try
        ////////    {
        ////////        int rows = db.ExecuteNonQuery(dbCommand);
        ////////        return rows;

        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes the storedProcedureName using the given parameterValues and returns the number of rows affected.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    try
        ////////    {
        ////////        int rows = db.ExecuteNonQuery(dbCommand);
        ////////        return rows;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////    //return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName, parameterValues);
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure with a DbCommand object and returns the number of rows affected.
        ///////// </summary>
        ///////// <param name="dbCommand">The DbCommand object to use.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQuery(DbCommand dbCommand);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    try
        ////////    {
        ////////        int rows = db.ExecuteNonQuery(dbCommand);
        ////////        return rows;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// Executes a stored procedure with a SqlCommand object and returns the number of rows affected.
        /////////////// </summary>
        /////////////// <param name="sqlCommand">The SqlCommand object to use.</param>
        /////////////// <returns>The number of rows affected.</returns>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static int ExecuteNonQuery(SqlCommand sqlCommand)
        ////////////{
        ////////////    Database db = DatabaseFactory.CreateDatabase();
        ////////////    try
        ////////////    {
        ////////////        int rows = db.ExecuteNonQuery(sqlCommand);
        ////////////        return rows;
        ////////////    }
        ////////////    finally
        ////////////    {
        ////////////        sqlCommand.Connection.Close();
        ////////////        sqlCommand.Dispose();
        ////////////        if (db != null) db = null;
        ////////////    }
        ////////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Uses ExecuteNonQuery in a specified Transaction
        ///////// </summary>
        ///////// <param name="transaction">The IDbTransaction to execute the command within.</param>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQuery(IDbTransaction transaction, string storedProcedureName);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        ////////    DbTransaction trans = (DbTransaction)transaction;
        ////////    dbCommand.Transaction = trans;
        ////////    try
        ////////    {
        ////////        int rows = db.ExecuteNonQuery(dbCommand);
        ////////        return rows;
        ////////        //}
        ////////        //finally
        ////////        //{
        ////////        //    dbCommand.Connection.Close();
        ////////        //}
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////    //DbTransaction trans = (DbTransaction)transaction;
        ////////    //return DatabaseFactory.CreateDatabase().ExecuteNonQuery(trans, CommandType.StoredProcedure, storedProcedureName);
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes the <i>storedProcedureName</i> using the given parameterValues within a transaction and returns the number of rows affected.
        ///////// </summary>
        ///////// <param name="transaction">The IDbTransaction to execute the command within.</param>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQuery(IDbTransaction transaction, string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    //Database db = DatabaseFactory.CreateDatabase();
        ////////    //DbCommand dbCommand = db..GetStoredProcCommand(storedProcedureName);
        ////////    //try
        ////////    //{
        ////////    //    int rows = db.ExecuteNonQuery(dbCommand);
        ////////    //    return rows;
        ////////    //}
        ////////    ////return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName);
        ////////    //finally
        ////////    //{
        ////////    //    dbCommand.Connection.Close();
        ////////    //}
        ////////    DbTransaction trans = (DbTransaction)transaction;
        ////////    return DatabaseFactory.CreateDatabase().ExecuteNonQuery(trans, storedProcedureName, parameterValues);
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Uses ExecuteNonQuery in a Transaction. If an exception oocurs the transaction is rolled back.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <returns>The number of rows affected.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQueryTrans(string storedProcedureName);
        ////////{
        ////////    int rows = 0;
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    using (DbConnection dbConn = db.CreateConnection())
        ////////    {
        ////////        DbTransaction transaction = dbConn.BeginTransaction();
        ////////        try
        ////////        {
        ////////            rows = ExecuteNonQuery(storedProcedureName);
        ////////            // Commit the transaction.
        ////////            transaction.Commit();
        ////////        }
        ////////        catch
        ////////        {
        ////////            // Roll back the transaction. 
        ////////            transaction.Rollback();
        ////////        }
        ////////        finally
        ////////        {
        ////////            dbConn.Close();
        ////////        }
        ////////        return rows;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// 
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>True if the query was executed sucessfully, else false.</returns>
        /////////----------------------------------------------------------------------------------
        //////  bool ExecuteNonQuerybool(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
        ////////    try
        ////////    {
        ////////        bool returnValue = db.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
        ////////        return returnValue;
        ////////        //}
        ////////        ////return DatabaseFactory.CreateDatabase().ExecuteNonQuery(storedProcedureName);
        ////////        //finally
        ////////        //{
        ////////        //    dbCommand.Connection.Close();
        ////////        //}
        ////////    }
        ////////    finally
        ////////    {
        ////////        //dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////    //return DB.ExecuteNonQuery(storedProcedureName, parameterValues) == 0 ? false : true;
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure and returns the value of the parameter called ReturnValue.
        ///////// </summary>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>The value of the parameter called ReturnValue.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQueryReturnValue(string storedProcedureName, params object[] parameterValues);
        ////////{
        ////////    Database db = DatabaseFactory.CreateDatabase();
        ////////    //DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    //int i = parameterValues.GetUpperBound(0);

        ////////    //ArrayList li = new ArrayList();

        ////////    //foreach (object Object in parameterValues)
        ////////    //{
        ////////    //    li.Add(Object);
        ////////    //}

        ////////    //li.Add(null);

        ////////    //Array objectArray = Array.CreateInstance(typeof(object), li.Count);

        ////////    //li.CopyTo(objectArray);

        ////////    //parameterValues.
        ////////    //parameterValues.SetValue(0, i + 1);
        ////////    //DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, objectArray);
        ////////    DbCommand dbCmd = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    //ParameterCache ParameterCache = new ParameterCache();
        ////////    //DbParameter[] paramters = ParameterCache..GetCachedParameterSet((db.ConnectionStringWithoutCredentials, storedProcedureName);
        ////////    //db.DiscoverParameters(dbCommand);
        ////////    //ParameterCache.
        ////////    //DbParameterCollection dbParams;
        ////////    //foreach(object Object in parameterValues)
        ////////    //{
        ////////    //    DbParameter dbParam = dbCmd.CreateParameter();
        ////////    //    dbParam.Direction = ParameterDirection.Input;
        ////////    //    dbParam.Value = Object;
        ////////    //    //dbParams.Add(Object);
        ////////    //    dbCmd.Parameters.Add(dbParam);
        ////////    //}

        ////////    ////dbCmd.Parameters.AddRange(dbParams);
        ////////    //DbParameter param = dbCmd.CreateParameter();
        ////////    //param.Direction = ParameterDirection.Output;
        ////////    //param.DbType = DbType.Int32;
        ////////    //param.ParameterName = "ReturnValue";
        ////////    //dbCmd.Parameters.Add(param);

        ////////    //db.
        ////////    //sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4, "return");
        ////////    //sqlCommand.Parameters.AddRange(parameterValues);
        ////////    //dbCommand.Parameters.AddRange(parameterValues);
        ////////    try
        ////////    {
        ////////        //db.ExecuteNonQuery(dbCmd);
        ////////        //int retunValue = db.ExecuteNonQuery(dbCmd);
        ////////        int retunValue = (int)db.ExecuteScalar(dbCmd);

        ////////        //return (int)sqlCommand.Parameters["ReturnValue"].Value;
        ////////        //int retunValue = (int)dbCmd.Parameters["ReturnValue"].Value;
        ////////        //dbCommand.Connection.Close();
        ////////        return retunValue;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCmd.Connection.Close();
        ////////        dbCmd.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////// <summary>
        /////////// 
        /////////// </summary>
        /////////// <param name="storedProcedureName"></param>
        /////////// <param name="paramNameToReturn"></param>
        /////////// <param name="parameterValues"></param>
        /////////// <returns></returns>
        ////////public static int ExecuteNonQueryReturnValue(string storedProcedureName, string paramNameToReturn, params object[] parameterValues)
        ////////{
        ////////    Database db;
        ////////    DbCommand dbCommand;
        ////////    db = DatabaseFactory.CreateDatabase();
        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    try
        ////////    {
        ////////        db.ExecuteNonQuery(dbCommand);
        ////////        int retunValue = (int)dbCommand.Parameters[paramNameToReturn].Value;
        ////////        return retunValue;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes a stored procedure in a transaction and returns the value of the parameter called ReturnValue.
        ///////// </summary>
        ///////// <param name="transaction">The IDbTransaction to execute the command within.</param>
        ///////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        ///////// <param name="paramIndexToReturn">The index number of the return parameter.</param>
        ///////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        ///////// The parameter values must be in call order as they appear in the stored procedure.</param>
        ///////// <returns>The value of the parameter called ReturnValue.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int ExecuteNonQueryReturnValue(IDbTransaction transaction,
        //////    string storedProcedureName,
        //////    int paramIndexToReturn,
        //////    params object[] parameterValues);
        ////////{
        ////////    Database db;
        ////////    DbCommand dbCommand;
        ////////    db = DatabaseFactory.CreateDatabase();
        ////////    dbCommand = db.GetStoredProcCommand(storedProcedureName, parameterValues);
        ////////    DbTransaction trans = (DbTransaction)transaction;
        ////////    //ParameterCache ParameterCache = new ParameterCache();
        ////////    //DbParameter[] paramters = ParameterCache.GetCachedParameterSet(db.ConnectionStringWithoutCredentials, storedProcedureName);
        ////////    //db.DiscoverParameters(dbCommand);
        ////////    //db.
        ////////    //sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int, 4, "return");
        ////////    //sqlCommand.Parameters.AddRange(parameterValues);
        ////////    //dbCommand.Parameters.AddRange(parameterValues);

        ////////    //db.ExecuteNonQuery(trans, storedProcedureName, parameterValues);
        ////////    try
        ////////    {
        ////////        db.ExecuteNonQuery(dbCommand, trans);

        ////////        //return (int)sqlCommand.Parameters["ReturnValue"].Value;
        ////////        //return (int)dbCommand.Parameters["ReturnValue"].Value;
        ////////        //int returnValue = (int)dbCommand.Parameters["return"].Value;
        ////////        int returnValue = (int)dbCommand.Parameters[paramIndexToReturn].Value;
        ////////        //dbCommand.Connection.Close();
        ////////        return returnValue;
        ////////    }
        ////////    finally
        ////////    {
        ////////        dbCommand.Connection.Close();
        ////////        dbCommand.Dispose();
        ////////        if (db != null) db = null;
        ////////    }

        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// 
        /////////////// </summary>
        /////////////// <param name="storedProcedureName">The name of the stored procedure to execute.</param>
        /////////////// <param name="parameterValues">An array of parameters to pass to the stored procedure. 
        /////////////// The parameter values must be in call order as they appear in the stored procedure.</param>
        /////////////// <returns></returns>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static int RunSPReturnInteger(string storedProcedureName, params object[] parameterValues)
        ////////////{
        ////////////    Database db;
        ////////////    //SqlCommandWrapper sqlCommandWrapper;
        ////////////    SqlCommand sqlCommand;
        ////////////    db = DatabaseFactory.CreateDatabase();
        ////////////    //sqlCommandWrapper = (SqlCommandWrapper)db.GetStoredProcCommandWrapper(storedProcedureName);
        ////////////    sqlCommand = (SqlCommand)db.GetStoredProcCommand(storedProcedureName);

        ////////////    //sqlCommandWrapper.AddParameter("@retval", DbType.Int32, ParameterDirection.ReturnValue, "", DataRowVersion.Current, "return");
        ////////////    sqlCommand.Parameters.Add("@retval", SqlDbType.Int, 4, "return");

        ////////////    //db.ExecuteNonQuery(sqlCommandWrapper);
        ////////////    try
        ////////////    {
        ////////////        db.ExecuteNonQuery(sqlCommand);

        ////////////        //return (int)sqlCommandWrapper.GetParameterValue("retval");
        ////////////        int returnValue = (int)sqlCommand.Parameters["retval"].Value;
        ////////////        //sqlCommand.Connection.Close();
        ////////////        return returnValue;
        ////////////    }
        ////////////    finally
        ////////////    {
        ////////////        sqlCommand.Connection.Close();
        ////////////        sqlCommand.Dispose();
        ////////////        if (db != null) db = null;
        ////////////    }
        ////////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, 
        ///////// updated, or deleted row in a single table in a DataSet. 
        ///////// When calling this method, your DataSet table must have it's PrimaryKey specified and be the first column in the table. 
        ///////// This is the item that will be used to uniquely identify each row in the 
        ///////// DataSet table that has changed.
        ///////// </summary>
        ///////// <param name="dataSet">The DataSet to update.</param>
        ///////// <param name="insertstoredProcedureName">The name of the insert stored procedure to execute.</param>
        ///////// <param name="updatestoredProcedureName">The name of the update stored procedure to execute.</param>
        ///////// <param name="deletestoredProcedureName">The name of the delete stored procedure to execute.</param>
        ///////// <returns>Affected row count as integer.</returns>
        /////////----------------------------------------------------------------------------------
        //////  int UpdateDataSet(DataSet dataSet,
        //////    string insertstoredProcedureName,
        //////    string updatestoredProcedureName,
        //////    string deletestoredProcedureName);
        ////////{
        ////////    string[] columnParams;
        ////////    string tableName, primaryKeyField;
        ////////    ArrayList columnNames = new ArrayList();
        ////////    Database db = DatabaseFactory.CreateDatabase();

        ////////    // Get the table that will be modified
        ////////    DataTable table = dataSet.Tables[0];
        ////////    tableName = table.TableName;

        ////////    primaryKeyField = table.Columns[0].ColumnName;//table.PrimaryKey[0].ColumnName;

        ////////    //Insert Command
        ////////    //Get list of column names for paramaters
        ////////    foreach (DataColumn dc in table.Columns)
        ////////    {

        ////////        //if (dc.ColumnName != primaryKeyField)
        ////////        // if(dc.Ordinal != 0)
        ////////        columnNames.Add(dc.ColumnName);
        ////////    }

        ////////    columnNames.Add(table.Columns[0].ColumnName);

        ////////    columnParams = (string[])columnNames.ToArray(typeof(string));

        ////////    DbCommand insertCommand = db.GetStoredProcCommandWithSourceColumns(insertstoredProcedureName, columnParams);

        ////////    //Delete Command
        ////////    DbCommand deleteCommand = db.GetStoredProcCommand(deletestoredProcedureName);
        ////////    db.AddInParameter(deleteCommand, primaryKeyField, DbType.Int32, primaryKeyField, DataRowVersion.Current);

        ////////    //Update Command
        ////////    //Get list of column names for paramaters
        ////////    columnNames.Clear();
        ////////    foreach (DataColumn dc in table.Columns)
        ////////    {
        ////////        columnNames.Add(dc.ColumnName);
        ////////    }

        ////////    columnParams = (string[])columnNames.ToArray(typeof(string));
        ////////    DbCommand updateCommand = db.GetStoredProcCommandWithSourceColumns(updatestoredProcedureName, columnParams);

        ////////    int rowsAffected = db.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand,
        ////////        UpdateBehavior.Standard);

        ////////    updateCommand.Connection.Close();
        ////////    insertCommand.Connection.Close();
        ////////    deleteCommand.Connection.Close();

        ////////    return rowsAffected;
        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// 
        /////////////// </summary>
        /////////////// <param name="storedProcName">The name of the insert stored procedure to execute.</param>
        /////////////// <param name="dsProcParams">A dataset of parameters to pass to the stored procedure.</param>
        /////////////// <returns></returns>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static DataSet ExecuteStoredProc(string storedProcName, DataSet dsProcParams)
        ////////////{
        ////////////    SqlCommand sqlCommd = new SqlCommand();

        ////////////    try
        ////////////    {
        ////////////        sqlCommd.CommandType = CommandType.StoredProcedure;
        ////////////        sqlCommd.CommandText = storedProcName;

        ////////////        if (dsProcParams.Tables.Count > 0)
        ////////////        {
        ////////////            PopulateSQLCommandFromDataTable(sqlCommd, dsProcParams.Tables[0]);
        ////////////        }

        ////////////        return DB.ExecuteDataSet(sqlCommd);
        ////////////    }
        ////////////    finally
        ////////////    {
        ////////////        sqlCommd.Dispose();
        ////////////    }
        ////////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        ///////// </summary>
        ///////// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        ///////// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        ///////// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        ///////// <returns>Dataset from the website database</returns>
        /////////----------------------------------------------------------------------------------
        //////  DataSet ExecuteStoredProc(string storedProcedureName, string[] tableNames);
        ////////{
        ////////    object[] emptyArray = new object[0];
        ////////    return ExecuteStoredProc(storedProcedureName, emptyArray, tableNames);
        ////////}

        /////////----------------------------------------------------------------------------------
        ///////// <summary>
        ///////// Executes the named Stored Procedure with no parameters and returns multiple named DataTables
        ///////// </summary>
        ///////// <remarks>Uses the MS Patterns and Practices Data Access Application Block via the NET.Library Helper.</remarks>
        ///////// <param name="storedProcedureName">String name of the SQL Stored Proc</param>
        ///////// <param name="parameterValues"></param>
        ///////// <param name="tableNames">Comma delimeted list of names to assign to newly created tables</param>
        ///////// <returns>Dataset from the website database</returns>
        /////////----------------------------------------------------------------------------------
        //////  DataSet ExecuteStoredProc(string storedProcedureName, object[] parameterValues, string[] tableNames);
        ////////{
        ////////    DataSet returnDS = DB.ExecuteDataSet(storedProcedureName, parameterValues);
        ////////    for (int i = 0; i < returnDS.Tables.Count; i++)
        ////////    {
        ////////        if (tableNames[i] != null)
        ////////        {
        ////////            returnDS.Tables[i].TableName = tableNames[i].Trim();
        ////////        }
        ////////    }
        ////////    return returnDS;
        ////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// 
        /////////////// </summary>
        /////////////// <param name="pSQLCommand"></param>
        /////////////// <param name="pDataTable"></param>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static void PopulateSQLCommandFromDataTable(SqlCommand pSQLCommand, DataTable pDataTable)
        ////////////{
        ////////////    if (pDataTable.Rows.Count > 0)
        ////////////    {
        ////////////        PopulateSQLCommandFromDataRow(pSQLCommand, pDataTable.Columns, pDataTable.Rows[0]);
        ////////////    }
        ////////////}

        ///////////////----------------------------------------------------------------------------------
        /////////////// <summary>
        /////////////// 
        /////////////// </summary>
        /////////////// <param name="pSQLCommand"></param>
        /////////////// <param name="pColCollection"></param>
        /////////////// <param name="pDatarow"></param>
        ///////////////----------------------------------------------------------------------------------
        ////////////public static void PopulateSQLCommandFromDataRow(SqlCommand pSQLCommand, DataColumnCollection pColCollection, DataRow pDatarow)
        ////////////{
        ////////////    for (int i = 0; i < pColCollection.Count; i++)
        ////////////    {
        ////////////        pSQLCommand.Parameters.AddWithValue("@" + pColCollection[i].ColumnName, pDatarow.ItemArray[i]);
        ////////////    }
        ////////////}

        //////#endregion

        //////#endregion
    }
}
